# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html

import mysql.connector
from mysql.connector.errors import Error
from mysql.connector import errorcode
from qqmusic.items import SingerItem, AlbumItem, SongItem, PerformanceItem

"""
处理QQ  Music 中得到的数据
"""
class QqmusicPipeline(object):

    def open_spider(self, spider):
        try:
            self.connection = mysql.connector.connect(user="root", password="", host="localhost", database="db_music")
            self.cursor = self.connection.cursor()
        except Error as err:
            if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                print("Something is wrong with your user name or password")
            elif err.errno == errorcode.ER_BAD_DB_ERROR:
                print("Database does not exist")
            else:
                print(err)
        pass

    def close_spider(self, spider):
        if self.connection is not None:
            try:
                self.cursor.close()
                self.connection.close()
            except Exception as e:
                print(e)
        pass

    def process_item(self, item, spider):
        if isinstance(item, SingerItem):
            self.process_singer_item(item, spider)
        elif isinstance(item, AlbumItem):
            self.process_album_item(item, spider)
        elif isinstance(item, SongItem):
            self.process_song_item(item, spider)
            pass
        return item

    def process_singer_item(self, item, spider):
        assert isinstance(item, SingerItem), "Item type for Singer is Error!"
        insert_singer = """INSERT INTO tb_singer(`name`, `sex`, `pic`, `qqmusic_singer_id`, `qqmusic_singer_mid`) VALUES
            (%s, %s, %s, %s, %s);"""
        data = (item['name'], item['sex'], item['pic'], item['singer_id'], item['singer_mid'])
        self.cursor.execute(insert_singer, data)
        self.connection.commit()
        print("Insert singer: ", data, end="\n")
        pass

    def process_album_item(self, item, spider):
        assert isinstance(item, AlbumItem), "Item type for Album is Error!"
        insert_album = """INSERT INTO tb_album(`album_qqmusic_singer_mid`, `album_name`, `album_tranname`, `publish_date`, `singer_name`, `album_type`, `qqmusic_album_mid`, `qqmusic_album_id`, `qqmusic_pmid`) VALUES
            (%s, %s, %s, %s, %s, %s, %s, %s, %s);"""
        data = (item['album_qqmusic_singer_mid'], item['album_name'], item['album_trans_name'], item['publish_date'], item['singer_name'], item['album_type'], item['album_mid'], item['album_id'], item['pmid'])
        self.cursor.execute(insert_album, data)
        self.connection.commit()
        print("Insert album: ", data, end="\n")
        pass

    def process_song_item(self, item, spider):
        assert isinstance(item, SongItem), "Item type for Song is Error!"
        insert_song = """INSERT INTO tb_song(`name`, `title`, `subtitle`, `interval`, `isonly`, `language`, `genre`, `time_publish`, `qqmusic_song_mid`, `qqmusic_song_id`, `qqmusic_album_mid`, `qqmusic_album_id`) VALUES
            (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"""
        data = (item['name'], item['title'], item['subtitle'], item['interval'], item['isonly'], item['language'], item['genre'], item['time_publish'], item['qqmusic_song_mid'], item['qqmusic_song_id'], item['qqmusic_album_mid'], item['qqmusic_album_id'])
        self.cursor.execute(insert_song, data)
        print("Insert song: ", data, end="\n")

        insert_performance = """INSERT INTO tb_performance(`singer_mid`, `singer_id`, `singer_name`, `song_mid`, `song_id`, `song_name`) VALUES
            (%s, %s, %s, %s, %s, %s);"""
        for singer in item['singers']:
            data = (singer['singer_mid'], singer['singer_id'], singer['singer_name'], singer['song_mid'], singer['song_id'], singer['song_name'])
            self.cursor.execute(insert_performance, data)
            print("Insert performance: ", data, end="\n")
            pass
        self.connection.commit()
        pass
